import openpyxl
import tools_pmc


def find_key_by_value(dictionary, value):
    for key, val in dictionary.items():
        if val == value:
            return key
    return None


def find_in_list1():
    dict2 = {}
    for a in temp:
        for b in list1:
            for c in b:
                if a == c:
                    find_key_by_value(dict1, b)
                    if find_key_by_value(dict1, b) not in dict2.keys():
                        dict2[find_key_by_value(dict1, b)] = a
                    elif a > dict2[find_key_by_value(dict1, b)]:
                        dict2[find_key_by_value(dict1, b)] = a
    return dict2


def sn1(str1, str2):
    temp1 = []
    for p in dict1[str1]:
        if p > str2:
            temp1.append(p)
    return temp1


wb_bom = openpyxl.load_workbook(tools_pmc.lj_bom)
ws_bom = wb_bom.worksheets[0]

dict1 = {}
for i in range(2, ws_bom.max_row + 1):
    if ws_bom.cell(row=i, column=1).value[0] == "3":
        if ws_bom.cell(row=i, column=2).value not in dict1.keys():
            dict1[ws_bom.cell(row=i, column=2).value] = [ws_bom.cell(row=i, column=1).value]
        elif ws_bom.cell(row=i, column=1).value not in dict1[ws_bom.cell(row=i, column=2).value]:
            dict1[ws_bom.cell(row=i, column=2).value].append(ws_bom.cell(row=i, column=1).value)

wb_td = openpyxl.load_workbook(tools_pmc.lj_tdlkb)
ws_td = wb_td.worksheets[0]

wb = openpyxl.Workbook()
ws = wb.worksheets[0]

list1 = list(dict1.values())

a = 2
for i in range(3, ws_td.max_row + 1):
    if ws_td.cell(row=i, column=4).value is not None and "通用替代" not in ws_td.cell(row=i, column=4).value:
        temp = ws_td.cell(row=i, column=4).value.split("、")
        temp.pop()
        temp.sort()

        temp2 = 0
        for j in find_in_list1().keys():
            if sn1(j, find_in_list1()[j]) != []:
                for k in sn1(j, find_in_list1()[j]):
                    if ws_td.cell(row=i, column=1).value in tools_pmc.bom_1(k,ws_bom)[0]:
                        temp2 += 1
        if temp2 == 0:
            continue

        ws.cell(a, 1).value = ws_td.cell(row=i, column=1).value
        ws.cell(a, 2).value = ws_td.cell(row=i, column=2).value
        ws.cell(a, 3).value = ws_td.cell(row=i, column=3).value
        ws.cell(a, 4).value = ws_td.cell(row=i, column=5).value
        ws.cell(a, 5).value = ws_td.cell(row=i, column=6).value
        ws.cell(a, 6).value = ws_td.cell(row=i, column=7).value
        a += 1
        for j in find_in_list1().keys():
            if not sn1(j, find_in_list1()[j]):
                continue
            ws.cell(a, 1).value = j
            a += 1
            b = 1
            for k in sn1(j, find_in_list1()[j]):
                ws.cell(a, b).value = k
                b += 1
            a += 1
        a += 1

wb.save(r"C:\Users\10352\Desktop\1.xlsx")
